From time to time a request for a query to “show the current SQL” for each session appears on the internet. It’s not really difficult to write something suitable, but this particular request is an interesting demonstration of how much inertia there is in the Oracle world. Here’s a recent suggestion of what the query should look like:
|
1 2 3 4 5 6 7 8 9 10 11 |
select sess.sid, sess.serial#, sess.username, sess.status, sess.osuser, sess.machine, sess.module, sess.event, sess.logon_time, ss.sql_id, ss.sql_text from v$session sess, v$sql ss where sess.sql_id = ss.sql_id and sess.username = 'EFTUSER' ; |
The query has an error in it that appears extremely frequently in response to this request. Can you spot what it is ? (On the plus side, the query references v$sql rather than v$sqlarea – so it’s not too nasty.)
You can find the answer out on my blog; Oracle Scratchpad.
Load comments